package com.multicampus.minishop.services.purchase.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.multicampus.minishop.common.SearchVO;
import com.multicampus.minishop.product.vo.ProductVO;
import com.multicampus.minishop.services.purchase.vo.PurchaseVO;
import com.multicampus.minishop.services.user.dao.UserDAO;
import com.multicampus.minishop.util.DBUtil;

public class PurchaseDAO {
	public PurchaseVO findPurchase(int tranNo) throws Exception {
		try {
			Connection con = DBUtil.getConnection();

			String sql = "select * from TRANSACTION where TRAN_NO =?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, tranNo);
			
			ResultSet rs = stmt.executeQuery();

			UserDAO uDao = new UserDAO();
			PurchaseVO vo = null;
			while (rs.next()) {
				vo = new PurchaseVO();
				ProductVO prodVo = new ProductVO();

				vo.setTranNo( rs.getInt("TRAN_NO") );

				prodVo.setProdNo(rs.getInt("PROD_NO"));
				vo.setPurchaseProd( prodVo );

				vo.setBuyer( uDao.findUser(rs.getString("BUYER_ID")) );
				vo.setPaymentOption( rs.getString("PAYMENT_OPTION") );
				vo.setReceiverName( rs.getString("RECEIVER_NAME") );
				vo.setReceiverPhone( rs.getString("RECEIVER_PHONE") );
				vo.setDlvyAddr( rs.getString("DLVY_ADDR") );
				vo.setDlvyRequest( rs.getString("DLVY_REQUEST") );
				vo.setTranCode( rs.getString("TRAN_STATUS_CODE") );
				vo.setOrderDate( rs.getDate("ORDER_DATE") );
				vo.setDlvyDate( rs.getDate("DLVY_DATE") );
			}

			con.close();

			return vo;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}
	public HashMap getPurchaseList(SearchVO searchVO, String buyerId) throws Exception {
		HashMap map = null;
		try {
			Connection con = DBUtil.getConnection();

			String sql = "select * from TRANSACTION ";
			if (searchVO.getSearchCondition() != null) {
				String tvo = searchVO.getSearchCondition();
				if (tvo.equals("t01") || tvo.equals("t02") ||
						tvo.equals("t03") || tvo.equals("t04") ) {
					sql += " where TRAN_STATUS_CODE='" + tvo + "'";
					sql += " AND BUYER_ID='" + buyerId + "'";
				}
				else {
					sql += " where BUYER_ID='" + buyerId + "'";
				}
			}
			sql += " order by TRAN_NO desc";
System.out.println("absolute:" + (searchVO.getPage() * searchVO.getPageUnit() - 2));
System.out.println("sql:" + sql);

			PreparedStatement stmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stmt.executeQuery();

			rs.last();
			int total = rs.getRow();

			map = new HashMap();
			map.put("count", new Integer(total));



			rs.absolute(searchVO.getPage() * searchVO.getPageUnit() - 2);

			ArrayList list = new ArrayList();
			UserDAO uDao = new UserDAO();
			if (total > 0) {
				for (int i = 0; i < searchVO.getPageUnit(); i++) {
					PurchaseVO vo = new PurchaseVO();
					ProductVO prodVo = new ProductVO();
					
					vo.setTranNo( rs.getInt("TRAN_NO") );
					
					prodVo.setProdNo(rs.getInt("PROD_NO"));
					vo.setPurchaseProd( prodVo );

					vo.setBuyer( uDao.findUser(rs.getString("BUYER_ID")) );
					vo.setPaymentOption( rs.getString("PAYMENT_OPTION") );
					vo.setReceiverName( rs.getString("RECEIVER_NAME") );
					vo.setReceiverPhone( rs.getString("RECEIVER_PHONE") );
					vo.setDlvyAddr( rs.getString("DLVY_ADDR") );
					vo.setDlvyRequest( rs.getString("DLVY_REQUEST") );
					vo.setTranCode( rs.getString("TRAN_STATUS_CODE") );
					vo.setOrderDate( rs.getDate("ORDER_DATE") );
					vo.setDlvyDate( rs.getDate("DLVY_DATE") );

					list.add(vo);
					if (!rs.next())
						break;
				}
			}
			map.put("list", list);

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return map;
	}
	public HashMap getSaleList(SearchVO searchVO) throws Exception {
		HashMap map = null;
		try {
			Connection con = DBUtil.getConnection();

			String sql = "select * from TRANSACTION ";
			if (searchVO.getSearchCondition() != null) {
				String tvo = searchVO.getSearchCondition();
				if (tvo.equals("t01") || tvo.equals("t02") ||
						tvo.equals("t03") || tvo.equals("t04") ) {
					sql += " where TRAN_STATUS_CODE='" + tvo + "'";
				}
			}

			sql += " order by TRAN_NO desc";
	System.out.println("absolute:" + (searchVO.getPage() * searchVO.getPageUnit() - 2));
	System.out.println("sql:" + sql);

			PreparedStatement stmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stmt.executeQuery();

			rs.last();
			int total = rs.getRow();

			map = new HashMap();
			map.put("count", new Integer(total));



			rs.absolute(searchVO.getPage() * searchVO.getPageUnit() - 2);
			ArrayList list = new ArrayList();
			UserDAO uDao = new UserDAO();
			if (total > 0) {
				for (int i = 0; i < searchVO.getPageUnit(); i++) {
					PurchaseVO vo = new PurchaseVO();
					ProductVO prodVo = new ProductVO();
					
					vo.setTranNo( rs.getInt("TRAN_NO") );
					
					prodVo.setProdNo(rs.getInt("PROD_NO"));
					vo.setPurchaseProd( prodVo );
					
					vo.setBuyer( uDao.findUser(rs.getString("BUYER_ID")) );
					vo.setPaymentOption( rs.getString("PAYMENT_OPTION") );
					vo.setReceiverName( rs.getString("RECEIVER_NAME") );
					vo.setReceiverPhone( rs.getString("RECEIVER_PHONE") );
					vo.setDlvyAddr( rs.getString("DLVY_ADDR") );
					vo.setDlvyRequest( rs.getString("DLVY_REQUEST") );
					vo.setTranCode( rs.getString("TRAN_STATUS_CODE") );
					vo.setOrderDate( rs.getDate("ORDER_DATE") );
					vo.setDlvyDate( rs.getDate("DLVY_DATE") );

					list.add(vo);
					if (!rs.next())
						break;
				}
			}
			map.put("list", list);

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return map;
	}
	public void insertPurchase(PurchaseVO vo) throws Exception {
		try {
			Connection con = DBUtil.getConnection();
			String sql = "insert into TRANSACTION(tran_no, prod_no, buyer_id" +
												", payment_option, receiver_name" +
												", receiver_phone, dlvy_addr" +
												", dlvy_request, tran_status_code" +
												", order_date, dlvy_date )" +
												" values(MINISHOP_TRAN.NEXTVAL" +
												", ?, ?, ?, ?, ?, ?, ?, ?, sysdate, ?)";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, vo.getPurchaseProd().getProdNo());
			pstmt.setString(2, vo.getBuyer().getUserId());
			pstmt.setString(3, vo.getPaymentOption());
			pstmt.setString(4, vo.getReceiverName());
			pstmt.setString(5, vo.getReceiverPhone());
			pstmt.setString(6, vo.getDlvyAddr());
			pstmt.setString(7, vo.getDlvyRequest());
			pstmt.setString(8, vo.getTranCode());
			pstmt.setDate(9, vo.getDlvyDate());
			
			pstmt.executeUpdate();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}
	public void updatePurchase(PurchaseVO vo) throws Exception{
		try {
			Connection con = DBUtil.getConnection();
			String sql = "update TRANSACTION set " +
							" RECEIVER_NAME=? " +
							", RECEIVER_PHONE=? " +
							", DLVY_ADDR=? " +
							", DLVY_REQUEST=? " +
							", DLVY_DATE=? " +
							", PAYMENT_OPTION=? " +
						" where tran_no=? ";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, vo.getReceiverName());
			pstmt.setString(2, vo.getReceiverPhone());
			pstmt.setString(3, vo.getDlvyAddr());
			pstmt.setString(4, vo.getDlvyRequest());
			pstmt.setDate(5, vo.getDlvyDate());
			pstmt.setString(6, vo.getPaymentOption());
			pstmt.setInt(7, vo.getTranNo());
			System.out.println(sql);
			pstmt.executeUpdate();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} 
	}
	public void updateTranCode(PurchaseVO vo) throws Exception {
		try {
			Connection con = DBUtil.getConnection();
			String sql = "update TRANSACTION set TRAN_STATUS_CODE=? " +
						"where TRAN_NO=? ";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, vo.getTranCode());
			pstmt.setInt(2, vo.getTranNo());
			
			pstmt.executeUpdate();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} 
	}
}
